package com.hzzy.controller;


import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import com.hzzy.IDao.HzClassesMapper;
import com.hzzy.IDao.HzDormitoryMapper;
import com.hzzy.IDao.HzStudentMapper;
import com.hzzy.IDao.HzTeacherMapper;
import com.hzzy.bean.HzClasses;
import com.hzzy.bean.HzDormitory;
import com.hzzy.bean.HzStudent;
import com.hzzy.bean.HzTeacher;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;


@Controller
@RequestMapping("/insert")
public class InsertDataController {
	@Resource
	private HzStudentMapper hzStudentMapper;
	@Resource
	private HzTeacherMapper hzTeacherMapper;
	@Resource
	private HzClassesMapper hzClassesMapper;
	@Resource
	private HzDormitoryMapper hzDormitoryMapper;
	
	/**
	 * 学生信息导入
	 */
	@RequestMapping(value = "/insertStudentFile")  
    public String insertStudentFile(@RequestParam(value = "file", required = false) MultipartFile file, HttpServletRequest request, ModelMap model) {  
		
        String path = request.getSession().getServletContext().getRealPath("upload");  
        String fileName = file.getOriginalFilename();  
//	        String fileName = new Date().getTime()+".jpg";  
        
        File targetFile = new File(path, fileName);  
        if(!targetFile.exists()){  
            targetFile.mkdirs();  
        }  
  
        //保存  
        try {  
            file.transferTo(targetFile);  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
        model.addAttribute("fileUrl", request.getContextPath()+"/upload/"+fileName);  
  
        jxl.Workbook readwb = null;   
		  
        try    
  
        {   
        	String inputStreamFile = path + "/" +fileName;
            InputStream instream = new FileInputStream(inputStreamFile);   
            readwb = Workbook.getWorkbook(instream);   
            //Sheet的下标是从0开始   
            Sheet readsheet = readwb.getSheet(0);   
  
            //获取Sheet表中所包含的总列数   
            //int rsColumns = readsheet.getColumns();   
            //获取Sheet表中所包含的总行数   
            int rsRows = readsheet.getRows();   
            //获取指定单元格的对象引用   
            
            for (int i = 1; i < rsRows; i++){  
            	HzStudent hzStudent = new HzStudent();
            	
                for (int j = 0; j < 24; j++){   
                	
                	//取得相应单元格的数据
                    Cell cell = readsheet.getCell(j, i);   
  
                    String temp = cell.getContents();
                    switch(j)
                    {
	                    case 0:
	                    	hzStudent.setSno(temp);
	                    	hzStudent.setPassword(temp + "123");
	                    	break;
	                    case 1:hzStudent.setName(temp);
	                    break;
	                    case 2:hzStudent.setSex(temp);
	                    break;
	                    case 3:
	                    	
	                    	SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMdd");//小写的mm表示的是分钟  
	                    	java.util.Date date=sdf.parse(temp);
	                    	hzStudent.setBirthday(date);
	                    break;
	                    case 4:hzStudent.setPolitical(temp);
	                    break;
	                    case 5:hzStudent.setIdcard(temp);
	                    break;
	                    case 6:hzStudent.setBirthPlace(temp);;
	                    break;
	                    case 7:hzStudent.setNation(temp);
	                    break;
	                    case 8:hzStudent.setAddress(temp);
	                    break;
	                    case 9:hzStudent.setPostcode(temp);
	                    break;
	                    case 10:hzStudent.setQq(temp);
	                    break;
	                    case 11:hzStudent.setWechat(temp);
	                    break;
	                    case 12:hzStudent.setEmail(temp);
	                    break;
//	                    case 13:paramMap.put("sno(temp);break;
	                    case 14:hzStudent.setClassid(Integer.parseInt(temp));
	                    break;
	                    case 15:hzStudent.setMajor(temp);
	                    break;
	                    case 16:hzStudent.setDorid(Integer.parseInt(temp));
	                    break;
	                    case 17:hzStudent.setPhone(temp);
	                    break;
	                    case 18:hzStudent.setFatherName(temp);
	                    break;
	                    case 19:hzStudent.setFatherPhone(temp);
	                    break;
	                    case 20:hzStudent.setMotherName(temp);
	                    break;
	                    case 21:hzStudent.setMotherPhone(temp);
	                    break;
	                    case 22:hzStudent.setPreDomicile(temp);
	                    break;
	                    case 23:hzStudent.setPreSchoolStatus(temp);
	                    break;
                    }
                }   
                hzStudentMapper.insertStudent(hzStudent);
            }   
        } catch (Exception e) {   
  
            e.printStackTrace();   
        } finally {   
  
            readwb.close();   
        }   
        return "insertStudent.page";
    }  
		
	/**
	 * 教师信息导入
	 */
	@RequestMapping(value = "/insertTeacherFile")  
    public String insertTeacherFile(@RequestParam(value = "file", required = false) MultipartFile file, HttpServletRequest request, ModelMap model) {  
  
        String path = request.getSession().getServletContext().getRealPath("upload");  
        String fileName = file.getOriginalFilename();  
//	        String fileName = new Date().getTime()+".jpg";  
        
        File targetFile = new File(path, fileName);  
        if(!targetFile.exists()){  
            targetFile.mkdirs();  
        }  
  
        //保存  
        try {  
            file.transferTo(targetFile);  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
        model.addAttribute("fileUrl", request.getContextPath()+"/upload/"+fileName);  
  
        jxl.Workbook readwb = null;   
		  
        try    
  
        {   
        	String inputStreamFile = path + "/" +fileName;
            InputStream instream = new FileInputStream(inputStreamFile);   
            readwb = Workbook.getWorkbook(instream);   
            //Sheet的下标是从0开始   
            Sheet readsheet = readwb.getSheet(0);   
  
            //获取Sheet表中所包含的总列数   
            //int rsColumns = readsheet.getColumns();   
            //获取Sheet表中所包含的总行数   
            int rsRows = readsheet.getRows();   
            //获取指定单元格的对象引用   
            
            for (int i = 1; i < rsRows; i++){  
            	HzTeacher hzTeacher = new HzTeacher();
            	
                for (int j = 0; j < 4; j++){   
                	
                	//取得相应单元格的数据
                    Cell cell = readsheet.getCell(j, i);   
  
                    String temp = cell.getContents();
                    switch(j)
                    {
	                    case 0:hzTeacher.setName(temp);
	                    	break;
	                    case 1:hzTeacher.setTeacherId(temp);
	                    	break;
	                    case 2:hzTeacher.setPhone(temp);
	                    	break;
	                    case 3:
	                    	if ("".equals(temp) || temp == null) {
	                    		hzTeacher.setPassword(hzTeacher.getTeacherId() + "654321");
	                    	} else {
	                    		hzTeacher.setPassword(temp);
	                    	}
                    }
                }   
                hzTeacherMapper.insertTeacher(hzTeacher);
            }   
        } catch (Exception e) {   
  
            e.printStackTrace();   
        } finally {   
  
            readwb.close();   
        }   
        return "insertTeacher.page";
    }  
	
	/**
	 * 班级信息导入
	 */
	@RequestMapping(value = "/insertClassFile")  
    public String insertClassFile(@RequestParam(value = "file", required = false) MultipartFile file, HttpServletRequest request, ModelMap model) {  
  
        String path = request.getSession().getServletContext().getRealPath("upload");  
        String fileName = file.getOriginalFilename();  
//	        String fileName = new Date().getTime()+".jpg";  
        
        File targetFile = new File(path, fileName);  
        if(!targetFile.exists()){  
            targetFile.mkdirs();  
        }  
  
        //保存  
        try {  
            file.transferTo(targetFile);  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
        model.addAttribute("fileUrl", request.getContextPath()+"/upload/"+fileName);  
  
        jxl.Workbook readwb = null;   
		  
        try    
  
        {   
        	String inputStreamFile = path + "/" +fileName;
            InputStream instream = new FileInputStream(inputStreamFile);   
            readwb = Workbook.getWorkbook(instream);   
            //Sheet的下标是从0开始   
            Sheet readsheet = readwb.getSheet(0);   
  
            //获取Sheet表中所包含的总列数   
            //int rsColumns = readsheet.getColumns();   
            //获取Sheet表中所包含的总行数   
            int rsRows = readsheet.getRows();   
            //获取指定单元格的对象引用   
            
            for (int i = 1; i < rsRows; i++){  
            	HzClasses hzClasses = new HzClasses();
            	
                for (int j = 0; j < 4; j++){   
                	
                	//取得相应单元格的数据
                    Cell cell = readsheet.getCell(j, i);   
  
                    String temp = cell.getContents();
                    switch(j)
                    {
	                    case 0:hzClasses.setClassName(temp);
	                    	break;
	                    case 1:hzClasses.setClassSn(Integer.parseInt(temp));
	                    	break;
	                    case 2:hzClasses.setTeacherName(temp);
                    		break;
	                    case 3:hzClasses.setTeacherId(Integer.parseInt(temp));
	                    	break;
                    }
                }   
                hzClassesMapper.insertClasses(hzClasses);
            }   
        } catch (Exception e) {   
  
            e.printStackTrace();   
        } finally {   
  
            readwb.close();   
        }   
        return "insertClass.page";
    }  
	
	/**
	 * 寝室信息导入
	 */
	@RequestMapping(value = "/insertDormitoryFile")  
    public String insertDormitoryFile(@RequestParam(value = "file", required = false) MultipartFile file, HttpServletRequest request, ModelMap model) {  
  
        String path = request.getSession().getServletContext().getRealPath("upload");  
        String fileName = file.getOriginalFilename();  
//	        String fileName = new Date().getTime()+".jpg";  
        
        File targetFile = new File(path, fileName);  
        if(!targetFile.exists()){  
            targetFile.mkdirs();  
        }  
  
        //保存  
        try {  
            file.transferTo(targetFile);  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
        model.addAttribute("fileUrl", request.getContextPath()+"/upload/"+fileName);  
  
        jxl.Workbook readwb = null;   
		  
        try    
  
        {   
        	String inputStreamFile = path + "/" +fileName;
            InputStream instream = new FileInputStream(inputStreamFile);   
            readwb = Workbook.getWorkbook(instream);   
            //Sheet的下标是从0开始   
            Sheet readsheet = readwb.getSheet(0);   
  
            //获取Sheet表中所包含的总列数   
            //int rsColumns = readsheet.getColumns();   
            //获取Sheet表中所包含的总行数   
            int rsRows = readsheet.getRows();   
            //获取指定单元格的对象引用   
            
            for (int i = 1; i < rsRows; i++){  
            	HzDormitory hzDormitory = new HzDormitory();
            	
                for (int j = 0; j < 5; j++){   
                	
                	//取得相应单元格的数据
                    Cell cell = readsheet.getCell(j, i);   
  
                    String temp = cell.getContents();
                    switch(j)
                    {
	                    case 0:hzDormitory.setQuarter(temp);
	                    	break;
	                    case 1:hzDormitory.setBuilding(Integer.parseInt(temp));
	                    	break;
	                    case 2:hzDormitory.setDormNum(Integer.parseInt(temp));
	                    	break;
	                    case 3:hzDormitory.setTeacherId(temp);
	                    	break;
                    }
                }   
                hzDormitoryMapper.insertDormitory(hzDormitory);
            }   
        } catch (Exception e) {   
  
            e.printStackTrace();   
        } finally {   
  
            readwb.close();   
        }   
        return "insertDormitory.page";
    }  
	
	/**
	 * 寝室长
	 */
	@RequestMapping(value = "/updateDormitoryFile")  
    public String updateDormitoryFile(@RequestParam(value = "file", required = false) MultipartFile file, HttpServletRequest request, ModelMap model) {  
  
        String path = request.getSession().getServletContext().getRealPath("upload");  
        String fileName = file.getOriginalFilename();  
//	        String fileName = new Date().getTime()+".jpg";  
        
        File targetFile = new File(path, fileName);  
        if(!targetFile.exists()){  
            targetFile.mkdirs();  
        }  
  
        //保存  
        try {  
            file.transferTo(targetFile);  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
        model.addAttribute("fileUrl", request.getContextPath()+"/upload/"+fileName);  
  
        jxl.Workbook readwb = null;   
		  
        try    
  
        {   
        	String inputStreamFile = path + "/" +fileName;
            InputStream instream = new FileInputStream(inputStreamFile);   
            readwb = Workbook.getWorkbook(instream);   
            //Sheet的下标是从0开始   
            Sheet readsheet = readwb.getSheet(0);   
  
            //获取Sheet表中所包含的总列数   
            //int rsColumns = readsheet.getColumns();   
            //获取Sheet表中所包含的总行数   
            int rsRows = readsheet.getRows();   
            //获取指定单元格的对象引用   
            
            for (int i = 1; i < rsRows; i++){  
            	HzDormitory hzDormitory = new HzDormitory();
            	
                for (int j = 0; j < 3; j++){   
                	
                	//取得相应单元格的数据
                    Cell cell = readsheet.getCell(j, i);   
  
                    String temp = cell.getContents();
                    switch(j)
                    {
	                    case 0:hzDormitory.setId(Integer.parseInt(temp));
	                    	break;
	                    case 2:hzDormitory.setStudentId(temp);
	                    	break;
                    }
                }   
                hzDormitoryMapper.updateByDormitoryKey(hzDormitory);
            }   
        } catch (Exception e) {   
  
            e.printStackTrace();   
        } finally {   
  
            readwb.close();   
        }   
        return "updateDormitory.page";
    }  
	
	@RequestMapping(value = {"/insertStudent"})
	public String insertStudent(Model model) {
		
		return "insertStudent.page";
	}
	@RequestMapping(value = {"/insertClass"})
	public String insertClass(Model model) {
		
		return "insertClass.page";
	}
	@RequestMapping(value = {"/insertDormitory"})
	public String insertDormitory(Model model) {
		
		return "insertDormitory.page";
	}
	@RequestMapping(value = {"/insertTeacher"})
	public String insertTeacher(Model model) {
		
		return "insertTeacher.page";
	}
	@RequestMapping(value = {"/updateDormitory"})
	public String updateDormitory(Model model) {
		
		return "updateDormitory.page";
	}
}
